package com.five.wisdomClass.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.five.wisdomClass.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;


@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    // 获取学生对象
    @Select("SELECT id,s_number,s_name,s_gender,s_phone,s_hometown,s_nationality,s_political_face,email,birth_date,home_address,academy,major,class AS classname,id_card_number,face_photo,name FROM smartcampus.studentinfo WHERE s_number = #{sNumber}")
    Student getStudent(String sNumber);

    // 通过专业班级查询学生的姓名，学号，电话
    @Select("SELECT s_number,s_name,s_phone,class AS classname FROM smartcampus.studentinfo WHERE class = #{classname}")
    IPage<Student> getClassMates(Page<?> page,@Param("classname") String classname);

    // 查看院系，专业，班级，个人信息等
    @Select("SELECT s_number,s_name,academy,major,class AS classname FROM smartcampus.studentinfo WHERE s_number = #{sNumber};")
    Student getSelfInfo(@Param("sNumber") String sNumber);

    // 查看公共课信息:
    // 通过学生信息表的班级名，查询班级表中的班级号，去关系表中查班级号对应的公共课程号
    // 最后用公共课程号去课程表中查询课程信息
    @Select("SELECT class_id FROM smartcampus.specialized_class WHERE class = #{classname};")
    String getClassId(@Param("classname") String classname);

    @Select("SELECT course_number FROM smartcampus.class_public_course WHERE class_id = #{classId}")
    List<String> getPublicCourseNumber(@Param("classId") String classId);

    @Select("SELECT course,credits,teacher_name,start_time,end_time,location FROM smartcampus.public_course WHERE course_number = #{courseNumber} AND state = 1")
    List<Course> getPublicCourse(@Param("courseNumber") String courseNumber);

    // 查看选修课信息：
    // 通过学生学号查询关系表中的课程号，再通过课程号查询课程表中的课程信息
    @Select("SELECT course_number FROM smartcampus.student_take_course WHERE s_number = #{sNumber}")
    List<String> getTakeCourseNumbers(@Param("sNumber") String sNumber);

    @Select("Select course,credits,teacher_name,start_time,end_time,location FROM smartcampus.take_course WHERE course_number = #{courseNumber} AND state = 1")
    List<Course> getTakeCourse(@Param("courseNumber") String courseNumber);

    // 查询课程成绩
    @Select("SELECT * FROM smartcampus.scores WHERE s_number = #{sNumber}")
    List<Scores> getScores(@Param("sNumber") String sNumber);

    // 添加个人计划
    @Insert("INSERT INTO smartcampus.self_plan(s_number,date,context) VALUES(#{sNumber},#{date},#{context})")
    int addPlan(SelfPlan selfPlan);

    // 删除个人计划
    @Delete("DELETE FROM smartcampus.self_plan WHERE id = #{id}")
    int deletePlan(Integer id);

    // 编辑个人计划
    @Update("UPDATE smartcampus.self_plan SET context = #{context} WHERE id = #{id}")
    int editPlan(String context, Integer id);

    // 查看个人计划
    @Select("SELECT * FROM smartcampus.self_plan WHERE s_number = #{sNumber}")
    IPage<SelfPlan> showPlan(Page<?> page,@Param("sNumber") String sNumber);

    // 请假
    @Insert("INSERT INTO smartcampus.leave(s_number,s_name,leave_type,start_time,end_time,contact,context) VALUES(#{sNumber},#{sName},#{leaveType},#{startTime},#{endTime},#{contact},#{context})")
    int submitLeave(Leave leave);
    // 查看请假列表
    @Select("SELECT id,leave_type,start_time,end_time,contact,context,state,reason FROM smartcampus.leave WHERE s_number = #{sNumber}")
    IPage<Leave> getSelfLeaves(Page<Leave> page, String sNumber);

    /**
     * 查看个人所学课程列表
     */
    // 公共课
    @Select("SELECT course_number FROM smartcampus.class_public_course WHERE class_id = #{classId}")
    List<String> getSelfPublicCourseNumber(@Param("classId") String classId);
    @Select("SELECT distinct course_number,course,teacher_name FROM smartcampus.public_course WHERE course_number = #{courseNumber}")
    List<Course> getSelfPublicCourse(@Param("courseNumber") String courseNumber);
    // 选修课
    @Select("SELECT course_number FROM smartcampus.student_take_course WHERE s_number = #{sNumber}")
    List<String> getSelfTakeCourseNumbers(@Param("sNumber") String sNumber);
    @Select("Select distinct course_number,course,teacher_name FROM smartcampus.take_course WHERE course_number = #{courseNumber}")
    List<Course> getSelfTakeCourse(@Param("courseNumber") String courseNumber);

    // 学生查看某课程的签到记录
    @Select("SELECT id,teacher_name,post_time,actual_time,sign_state FROM smartcampus.sign_info WHERE course_number = #{courseNumber} AND s_number = #{sNumber} ORDER BY post_time DESC")
    IPage<SignInfo> getSignInfos(Page<SignInfo> page, String courseNumber, String sNumber);

    // 进行签到
    @Update("UPDATE smartcampus.sign_info SET sign_state = 1,actual_time = current_time WHERE id = #{id} AND code = #{code}")
    int studentSign(SignInfo signInfo);

    // 签到超时
    @Update("UPDATE smartcampus.sign_info SET sign_state = 3 WHERE id = #{id}")
    int studentUnSign(SignInfo signInfo);

}
